package com.lifeverse.service;

import io.micrometer.core.instrument.MeterRegistry;
import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.springframework.cache.annotation.Cacheable;
import org.springframework.scheduling.annotation.Scheduled;
import org.springframework.stereotype.Service;

import javax.sql.DataSource;
import java.sql.*;
import java.time.LocalDateTime;
import java.util.*;
import java.util.concurrent.ConcurrentHashMap;

/**
 * 数据库优化服务
 * 提供数据库查询优化和性能监控功能
 */
@Service
@RequiredArgsConstructor
@Slf4j
public class DatabaseOptimizationService {
    
    private final DataSource dataSource;
    private final MeterRegistry meterRegistry;
    private final PerformanceMonitoringService performanceMonitoringService;
    
    // 慢查询记录
    private final Map<String, SlowQueryInfo> slowQueries = new ConcurrentHashMap<>();
    
    /**
     * 慢查询信息
     */
    public static class SlowQueryInfo {
        private String sql;
        private long executionTime;
        private LocalDateTime lastExecuted;
        private int executionCount;
        private String optimizationSuggestion;
        
        public SlowQueryInfo(String sql, long executionTime) {
            this.sql = sql;
            this.executionTime = executionTime;
            this.lastExecuted = LocalDateTime.now();
            this.executionCount = 1;
        }
        
        // Getters and Setters
        public String getSql() { return sql; }
        public void setSql(String sql) { this.sql = sql; }
        
        public long getExecutionTime() { return executionTime; }
        public void setExecutionTime(long executionTime) { this.executionTime = executionTime; }
        
        public LocalDateTime getLastExecuted() { return lastExecuted; }
        public void setLastExecuted(LocalDateTime lastExecuted) { this.lastExecuted = lastExecuted; }
        
        public int getExecutionCount() { return executionCount; }
        public void setExecutionCount(int executionCount) { this.executionCount = executionCount; }
        
        public String getOptimizationSuggestion() { return optimizationSuggestion; }
        public void setOptimizationSuggestion(String optimizationSuggestion) { this.optimizationSuggestion = optimizationSuggestion; }
        
        public void incrementCount() {
            this.executionCount++;
            this.lastExecuted = LocalDateTime.now();
        }
    }
    
    /**
     * 数据库连接池状态
     */
    public static class ConnectionPoolStatus {
        private int activeConnections;
        private int idleConnections;
        private int totalConnections;
        private int maxConnections;
        private double utilizationRate;
        
        // Getters and Setters
        public int getActiveConnections() { return activeConnections; }
        public void setActiveConnections(int activeConnections) { this.activeConnections = activeConnections; }
        
        public int getIdleConnections() { return idleConnections; }
        public void setIdleConnections(int idleConnections) { this.idleConnections = idleConnections; }
        
        public int getTotalConnections() { return totalConnections; }
        public void setTotalConnections(int totalConnections) { this.totalConnections = totalConnections; }
        
        public int getMaxConnections() { return maxConnections; }
        public void setMaxConnections(int maxConnections) { this.maxConnections = maxConnections; }
        
        public double getUtilizationRate() { return utilizationRate; }
        public void setUtilizationRate(double utilizationRate) { this.utilizationRate = utilizationRate; }
    }
    
    /**
     * 记录慢查询
     */
    public void recordSlowQuery(String sql, long executionTime) {
        String normalizedSql = normalizeSql(sql);
        
        SlowQueryInfo existingInfo = slowQueries.get(normalizedSql);
        if (existingInfo != null) {
            existingInfo.incrementCount();
            if (executionTime > existingInfo.getExecutionTime()) {
                existingInfo.setExecutionTime(executionTime);
            }
        } else {
            SlowQueryInfo newInfo = new SlowQueryInfo(normalizedSql, executionTime);
            newInfo.setOptimizationSuggestion(generateOptimizationSuggestion(sql));
            slowQueries.put(normalizedSql, newInfo);
        }
        
        // 记录到监控系统
        meterRegistry.counter("database.slow.query.count").increment();
        meterRegistry.timer("database.slow.query.time")
                .record(executionTime, java.util.concurrent.TimeUnit.MILLISECONDS);
        
        log.warn("慢查询检测: 执行时间={}ms, SQL={}", executionTime, normalizedSql);
    }
    
    /**
     * 获取慢查询列表
     */
    @Cacheable(value = "slowQueries", cacheManager = "performanceCacheManager")
    public List<SlowQueryInfo> getSlowQueries() {
        return new ArrayList<>(slowQueries.values());
    }
    
    /**
     * 获取数据库连接池状态
     */
    public ConnectionPoolStatus getConnectionPoolStatus() {
        ConnectionPoolStatus status = new ConnectionPoolStatus();
        
        try (Connection connection = dataSource.getConnection()) {
            DatabaseMetaData metaData = connection.getMetaData();
            
            // 尝试获取连接池信息（具体实现取决于连接池类型）
            if (dataSource instanceof org.apache.tomcat.jdbc.pool.DataSource) {
                org.apache.tomcat.jdbc.pool.DataSource tomcatDS = 
                        (org.apache.tomcat.jdbc.pool.DataSource) dataSource;
                
                status.setActiveConnections(tomcatDS.getActive());
                status.setIdleConnections(tomcatDS.getIdle());
                status.setTotalConnections(tomcatDS.getSize());
                status.setMaxConnections(tomcatDS.getMaxActive());
                
                if (status.getMaxConnections() > 0) {
                    status.setUtilizationRate((double) status.getActiveConnections() / 
                            status.getMaxConnections() * 100);
                }
            }
            
        } catch (SQLException e) {
            log.error("获取数据库连接池状态失败", e);
        }
        
        return status;
    }
    
    /**
     * 分析数据库性能
     */
    public Map<String, Object> analyzeDatabasePerformance() {
        Map<String, Object> analysis = new HashMap<>();
        
        try (Connection connection = dataSource.getConnection()) {
            // 获取数据库基本信息
            DatabaseMetaData metaData = connection.getMetaData();
            analysis.put("databaseProductName", metaData.getDatabaseProductName());
            analysis.put("databaseProductVersion", metaData.getDatabaseProductVersion());
            analysis.put("driverName", metaData.getDriverName());
            analysis.put("driverVersion", metaData.getDriverVersion());
            
            // 连接池状态
            ConnectionPoolStatus poolStatus = getConnectionPoolStatus();
            analysis.put("connectionPoolStatus", poolStatus);
            
            // 慢查询统计
            List<SlowQueryInfo> slowQueryList = getSlowQueries();
            analysis.put("slowQueryCount", slowQueryList.size());
            analysis.put("slowQueries", slowQueryList);
            
            // 数据库大小信息（MySQL特定）
            if (metaData.getDatabaseProductName().toLowerCase().contains("mysql")) {
                analyzeMySQLPerformance(connection, analysis);
            }
            
        } catch (SQLException e) {
            log.error("分析数据库性能失败", e);
            analysis.put("error", e.getMessage());
        }
        
        return analysis;
    }
    
    /**
     * 分析MySQL特定性能指标
     */
    private void analyzeMySQLPerformance(Connection connection, Map<String, Object> analysis) {
        try {
            // 查询数据库大小
            String sizeQuery = "SELECT ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS 'DB Size in MB' " +
                              "FROM information_schema.tables WHERE table_schema = DATABASE()";
            
            try (PreparedStatement stmt = connection.prepareStatement(sizeQuery);
                 ResultSet rs = stmt.executeQuery()) {
                if (rs.next()) {
                    analysis.put("databaseSizeMB", rs.getDouble(1));
                }
            }
            
            // 查询表统计信息
            String tableStatsQuery = "SELECT table_name, table_rows, " +
                                   "ROUND(((data_length + index_length) / 1024 / 1024), 2) AS 'Size in MB' " +
                                   "FROM information_schema.tables " +
                                   "WHERE table_schema = DATABASE() " +
                                   "ORDER BY (data_length + index_length) DESC LIMIT 10";
            
            List<Map<String, Object>> tableStats = new ArrayList<>();
            try (PreparedStatement stmt = connection.prepareStatement(tableStatsQuery);
                 ResultSet rs = stmt.executeQuery()) {
                while (rs.next()) {
                    Map<String, Object> tableInfo = new HashMap<>();
                    tableInfo.put("tableName", rs.getString("table_name"));
                    tableInfo.put("tableRows", rs.getLong("table_rows"));
                    tableInfo.put("sizeMB", rs.getDouble("Size in MB"));
                    tableStats.add(tableInfo);
                }
            }
            analysis.put("largestTables", tableStats);
            
            // 查询索引使用情况
            analyzeIndexUsage(connection, analysis);
            
        } catch (SQLException e) {
            log.error("分析MySQL性能失败", e);
        }
    }
    
    /**
     * 分析索引使用情况
     */
    private void analyzeIndexUsage(Connection connection, Map<String, Object> analysis) {
        try {
            String indexQuery = "SELECT table_name, index_name, cardinality " +
                              "FROM information_schema.statistics " +
                              "WHERE table_schema = DATABASE() AND cardinality IS NOT NULL " +
                              "ORDER BY cardinality DESC LIMIT 20";
            
            List<Map<String, Object>> indexStats = new ArrayList<>();
            try (PreparedStatement stmt = connection.prepareStatement(indexQuery);
                 ResultSet rs = stmt.executeQuery()) {
                while (rs.next()) {
                    Map<String, Object> indexInfo = new HashMap<>();
                    indexInfo.put("tableName", rs.getString("table_name"));
                    indexInfo.put("indexName", rs.getString("index_name"));
                    indexInfo.put("cardinality", rs.getLong("cardinality"));
                    indexStats.add(indexInfo);
                }
            }
            analysis.put("indexStatistics", indexStats);
            
        } catch (SQLException e) {
            log.error("分析索引使用情况失败", e);
        }
    }
    
    /**
     * 生成查询优化建议
     */
    private String generateOptimizationSuggestion(String sql) {
        String lowerSql = sql.toLowerCase().trim();
        List<String> suggestions = new ArrayList<>();
        
        // 检查是否缺少WHERE子句
        if (lowerSql.contains("select") && !lowerSql.contains("where") && !lowerSql.contains("limit")) {
            suggestions.add("考虑添加WHERE子句限制查询范围");
        }
        
        // 检查是否使用了SELECT *
        if (lowerSql.contains("select *")) {
            suggestions.add("避免使用SELECT *，只选择需要的列");
        }
        
        // 检查是否有ORDER BY但没有LIMIT
        if (lowerSql.contains("order by") && !lowerSql.contains("limit")) {
            suggestions.add("ORDER BY查询建议添加LIMIT子句");
        }
        
        // 检查是否使用了子查询
        if (lowerSql.contains("select") && lowerSql.indexOf("select") != lowerSql.lastIndexOf("select")) {
            suggestions.add("考虑将子查询优化为JOIN操作");
        }
        
        // 检查是否使用了LIKE '%xxx%'
        if (lowerSql.contains("like '%") && lowerSql.contains("%'")) {
            suggestions.add("避免使用前导通配符的LIKE查询，考虑全文索引");
        }
        
        return suggestions.isEmpty() ? "暂无优化建议" : String.join("; ", suggestions);
    }
    
    /**
     * 规范化SQL语句（移除参数值）
     */
    private String normalizeSql(String sql) {
        return sql.replaceAll("'[^']*'", "?")
                 .replaceAll("\\b\\d+\\b", "?")
                 .replaceAll("\\s+", " ")
                 .trim();
    }
    
    /**
     * 定时监控数据库性能
     */
    @Scheduled(fixedRate = 300000) // 每5分钟执行一次
    public void monitorDatabasePerformance() {
        try {
            ConnectionPoolStatus poolStatus = getConnectionPoolStatus();
            
            // 记录连接池指标
            meterRegistry.gauge("database.connection.active", poolStatus.getActiveConnections());
            meterRegistry.gauge("database.connection.idle", poolStatus.getIdleConnections());
            meterRegistry.gauge("database.connection.total", poolStatus.getTotalConnections());
            meterRegistry.gauge("database.connection.utilization", poolStatus.getUtilizationRate());
            
            // 检查连接池使用率
            if (poolStatus.getUtilizationRate() > 80) {
                log.warn("数据库连接池使用率过高: {:.2f}%", poolStatus.getUtilizationRate());
                meterRegistry.counter("database.connection.high_utilization").increment();
            }
            
            // 清理旧的慢查询记录
            cleanupOldSlowQueries();
            
        } catch (Exception e) {
            log.error("监控数据库性能失败", e);
        }
    }
    
    /**
     * 清理旧的慢查询记录
     */
    private void cleanupOldSlowQueries() {
        LocalDateTime cutoffTime = LocalDateTime.now().minusHours(24); // 保留24小时
        
        slowQueries.entrySet().removeIf(entry -> 
                entry.getValue().getLastExecuted().isBefore(cutoffTime));
    }
    
    /**
     * 获取数据库优化建议
     */
    public List<String> getDatabaseOptimizationRecommendations() {
        List<String> recommendations = new ArrayList<>();
        
        try {
            ConnectionPoolStatus poolStatus = getConnectionPoolStatus();
            
            // 连接池优化建议
            if (poolStatus.getUtilizationRate() > 80) {
                recommendations.add("连接池使用率过高，建议增加最大连接数或优化查询性能");
            }
            
            if (poolStatus.getIdleConnections() > poolStatus.getActiveConnections() * 2) {
                recommendations.add("空闲连接过多，建议减少最大连接数以节省资源");
            }
            
            // 慢查询优化建议
            List<SlowQueryInfo> slowQueryList = getSlowQueries();
            if (!slowQueryList.isEmpty()) {
                recommendations.add(String.format("发现 %d 个慢查询，建议进行优化", slowQueryList.size()));
                
                // 添加具体的慢查询优化建议
                slowQueryList.stream()
                        .limit(5) // 只显示前5个
                        .forEach(query -> recommendations.add(
                                String.format("慢查询优化: %s", query.getOptimizationSuggestion())));
            }
            
        } catch (Exception e) {
            log.error("获取数据库优化建议失败", e);
            recommendations.add("获取优化建议时发生错误: " + e.getMessage());
        }
        
        return recommendations;
    }
}